﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace ADO_Wrapper
{
    public static class DataManager
    {
        private static bool _connectionChecked = false;
        public static DataSet Query(string sqlQuery, ArrayList sqlParameters)
        {
            //You can take this fluff out when you become used to using this wrapper.
            if (ConnectionString.Value.ConnectionString == null)
            {

                throw new Exception("You must provide a connection string in order to use this method." + Environment.NewLine +
                 "Here is an example:" +
                 "ADO_Wrapper.ConnectionString.Value = new SqlConnection(\"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;\");");

            }
            //end of fluff
            if (!_connectionChecked) ConnectionOk();
            try
            {
                ConnectionString.Value.Open();
                var da = new SqlDataAdapter(sqlQuery, ConnectionString.Value);
                var ds = new DataSet();
                foreach (SqlParam sp in sqlParameters)
                {
                    da.SelectCommand.Parameters.AddWithValue(sp.Parameter, sp.Value);
                }
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionString.Value.Close();
            }
        }
        public static void Command(string sqlStatement, ArrayList sqlParameters)
        {
            //You can take this fluff out when you become used to using this wrapper.
            if (ConnectionString.Value.ConnectionString == null)
            {

                throw new Exception("You must provide a connection string in order to use this method." + Environment.NewLine +
                 "Here is an example:" +
                 "ADO_Wrapper.ConnectionString.Value = new SqlConnection(\"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;\");");

            }
            //end of fluff
            if (!_connectionChecked) ConnectionOk();
            try
            {
                ConnectionString.Value.Open();
                var com = new SqlCommand(sqlStatement,ConnectionString.Value);
                foreach(SqlParam sp in sqlParameters)
                {
                    com.Parameters.AddWithValue(sp.Parameter, sp.Value);
                }
                com.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
              throw new Exception(ex.Message);      
            }
            finally
            {
                ConnectionString.Value.Close();
            }
        }
        private static void ConnectionOk()
        {
            try
            {
                ConnectionString.Value.Open();
                _connectionChecked = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionString.Value.Close();
            }

        }
    }
    public class SqlParam
    {
        public string Parameter { get; set; }
        public object Value { get; set; }
    }
}
